package nakedb.sql2xml;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Random;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Very simple SQL to XML parser.
 * 
 * @author Ro
 */
public class SqlToXml {
  
  // Regular expression for matching the SQL
  private final Pattern RE_TABLE_NAME = Pattern.compile("^CREATE TABLE (.*) .*");
  private final Pattern RE_ALTER_TABLE = Pattern.compile("^ALTER TABLE (.*) ?.*");
  private final Pattern RE_FOREIGN_KEY = Pattern.compile("^ *" +
      "(ADD )?CONSTRAINT .* FOREIGN KEY \\((.*)\\) " +
      "REFERENCES (.*) ?\\((.*)\\).*");
  private final Pattern RE_ATTRIBUTE = Pattern.compile("^(  `.*` .*)");  
  

  // Collections of the tables and foreign keys
  private Map<String, Table> tables;
  private Set<ForeignKey> foreignKeys;  

  // Current name of the table we are processing (for the foreign keys)
  private String currentTableName = "";
  
  /**
   * Creates a new SQL to XML convertor.
   */
  public SqlToXml() {
    tables = new HashMap<String, Table>();
    foreignKeys = new HashSet<ForeignKey>();
  }
  
  /**
   * Loads a SQL file into a Java model. 
   * 
   * @param file The dump file from mysqldump.
   */
  public void processFile(String file) {
    try {
      FileInputStream fstream = new FileInputStream(file);
      
      DataInputStream in = new DataInputStream(fstream);
      BufferedReader br = new BufferedReader(new InputStreamReader(in));
      String strLine;
      
      //Read File Line By Line
      while ((strLine = br.readLine()) != null)   {
        strLine = strLine.replaceAll("`", "");
        
        Matcher m = RE_TABLE_NAME.matcher(strLine);
        // Matched a Table
        if (m.matches()) {
          currentTableName = m.group(1); // currentTableName global var not good
          addTable(currentTableName);
        } else {
          m = RE_FOREIGN_KEY.matcher(strLine);
          // Matched a Foreign Key
          if (m.matches()) {
            addForeignKey(currentTableName, m.group(2), m.group(3), m.group(4));
          } else {
            // Get the name of the table which foreign keys are outside of the 
            // create tables.
            m = RE_ALTER_TABLE.matcher(strLine);
            if (m.matches()) {
              currentTableName = m.group(1);
            } else {
              // Match SQL
              m = RE_ATTRIBUTE.matcher(strLine);
              if (m.matches()) {
                //store in the table 
                //    getTable(currentTableName).setSql(sql);
                // or currentTable.setSql(sql);
                // sql = m.group(1);
              } else {
                System.out.println("Skipped: " + strLine);   
              }
            }
          }
        }
      }
      
      in.close();
    } catch (IOException e){
      System.err.println("Error: " + e.getMessage());
    }    
  }
  
  /**
   * Creates and generates the XML loaded.
   * 
   * @return A String containing all the XML of the model. 
   */
  public String toXml() {
    StringBuilder tableNodes = new StringBuilder();
    StringBuilder tableEdges = new StringBuilder();
    
    for (Table table : tables.values()) {
      tableNodes.append(table.toXmlNode());
    }

    for (ForeignKey fk : foreignKeys) {
      tableEdges.append(fk.toXml());
    }

    tableNodes.append(tableEdges);
    
    return tableNodes.toString();
  }
  
  public String generateRandomGraph(int nodeNumber) {
    // Beware, use the new object only in this method, not "this".
    SqlToXml graph = new SqlToXml();
    
    // Create tables
    for (int i = 0; i < nodeNumber; i++) {
      graph.addTable("T" + i);
    }

    // Add foreign keys
    Random rand = new Random();
    
    for (int i = 0; i < nodeNumber; i++) {
      do {
        String targetTable = "T" + rand.nextInt(nodeNumber); 
        // If not same table
        if (! graph.getTable("T" + i).getName().equals(targetTable)) {
          graph.addForeignKey(graph.getTable("T" + i).getName(), "aa", targetTable, "bb");
        }
      } while (rand.nextInt(4) == 0);
    }
    
    return graph.toXml();
  }
  
  private void addTable(String tableName) {
    Table newTable = new Table(tableName);
    tables.put(tableName, newTable);
  }
  
  private Table getTable(String tableName) {
    return tables.get(tableName);
  }
  
  private void addForeignKey(String sourceTable, String sourceAttribute, 
      String targetTable, String targetAttribute) {
    ForeignKey fk = new ForeignKey(sourceTable, sourceAttribute, 
        targetTable, targetAttribute);
    foreignKeys.add(fk);
  }
  
  
  /**
   * Test program.
   * @throws IOException 
   */
  public static void main(String[] args) throws IOException {
    SqlToXml sql = new SqlToXml();
    
    // Convert SQL to XML
    /*sql.processFile("nakedb/sql2xml/sql1.sql");
    System.out.println(sql.toXml());*/
    
    // Generate XML
    FileWriter file = new FileWriter(new File("NakeDB_XML04.xml"));
    file.write(sql.generateRandomGraph(500));
    file.flush();
    file.close();
  }
}
